﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelper
{
    #region Properties

    public SqlDataReader data { get; set; }
    public DataTable dataTable { get; set; }
    private string connectionString { get; set; }
    private SqlConnection _connection { get; set; }
    private SqlCommand _cmd { get; set; }

    #endregion

    // Local
    public DBHelper(string dataSource, string database)
    {
        this.connectionString = @"Data Source=" + dataSource + "; Initial Catalog=" + database + "; Integrated Security=True";
    }

    // External with usr and pwd
    public DBHelper(string dataSource,string database ,string user, string password)
    {
        this.connectionString = @"Data Source=" + dataSource + ";database =" + database+ ";Persist Security Info=True;User ID=" + user + ";Password=" + password;
    }

    // Connect to database
    public bool Connect()
    {
        try
        {
            _connection = new SqlConnection(this.connectionString);
            _cmd = new SqlCommand();
            _cmd.Connection = _connection;
            return true;
        }
        catch (Exception e)
        {
            Console.WriteLine("En forbindelse til databasen, kunne ikke oprettes");
            throw e;
        }
    }

    // Åben forbindelse
    public void Open() { _connection.Open(); } //con error when no con to db

    // Luk forbindelse
    public void Close() { _connection.Close(); }

    // Indsæt ny row
    public bool InsertRow(string table, string fields, string values) { return HandleRow("INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")"); }

    // Update data
    public bool UpdateRow(string table, string fieldsAndValues, string where) { return HandleRow("UPDATE " + table + " SET " + fieldsAndValues + " WHERE " + where); }

    // Delete row
    public bool DeleteRow(string table, string where) { return HandleRow("DELETE FROM " + table + " WHERE " + where); }

    private bool HandleRow(string query)
    {
        _cmd.CommandText = query; // Sæt SQL
        int affectedRows = _cmd.ExecuteNonQuery(); // Udfør CMD
        return (affectedRows == 1) ? true : false;             // Retuner true eller false   
    }

    // Hent data
    public void SelectData(string fields, string table, string where, string orderBy)
    {
        string sql;
        string what = (fields == string.Empty) ? "*" : fields;

        sql = "SELECT " + what + " ";
        sql += "FROM " + table + " ";
        if (where != "")
            sql += "WHERE " + where + " ";
        if (orderBy != "")
            sql += "ORDER BY " + orderBy;

        // Sæt SQL
        _cmd.CommandText = sql;

        //sæt cmd type
        _cmd.CommandType = CommandType.Text;

        // Udfør CMD
        this.data = _cmd.ExecuteReader();
    }

    // Hent data - Stored Procedure
    public void SelectDataSP(string StoredProcedureName, params object[] pars)
    {
        // Sæt SQL
        _cmd.CommandText = StoredProcedureName;
        _cmd.Parameters.Clear();
        // Sæt CMD type
        _cmd.CommandType = CommandType.StoredProcedure;
        for (int i = 0; i < pars.Count(); i += 2)
        {
            SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
            _cmd.Parameters.Add(par);
        }
        // Clear parametre

        // Udfør CMD
        this.data = _cmd.ExecuteReader();
    }

    public DataTable ExcuteQuery(string sql, CommandType commandType, params object[] pars)
    {
        _cmd.CommandText = sql;
        _cmd.CommandType = commandType;

        for (int i = 0; i < pars.Count(); i += 2)
        {
            SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
            _cmd.Parameters.Add(par);
        }
        SqlDataAdapter Adap = new SqlDataAdapter(_cmd);
        DataSet dst = new DataSet();
        Adap.Fill(dst);
        return dst.Tables[0];
    }

    public bool ExcuteNonQuery(string sql, CommandType commandtype, params object[] pars)
    {
        int NoOfRowEffect = 0;
        _cmd.CommandText = sql;
        _cmd.CommandType = CommandType.Text;

        for (int i = 0; i < pars.Count(); i += 2)
        {
            SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
            _cmd.Parameters.Add(par);
        }
        NoOfRowEffect = _cmd.ExecuteNonQuery();
        return NoOfRowEffect != 0;
    }


}
